﻿SET IDENTITY_INSERT [webpages_Roles] ON;
GO
INSERT INTO [webpages_Roles] ([RoleId],[RoleName],[Description]) VALUES (1,N'Administrator',N'Has permission to do everything');
GO
INSERT INTO [webpages_Roles] ([RoleId],[RoleName],[Description]) VALUES (2,N'Editor',N'Has permission to fully manage page content');
GO
INSERT INTO [webpages_Roles] ([RoleId],[RoleName],[Description]) VALUES (3,N'Publisher',N'Can publish archive and delete pages');
GO
INSERT INTO [webpages_Roles] ([RoleId],[RoleName],[Description]) VALUES (4,N'Author',N'Can create new and edit existing page content');
GO
SET IDENTITY_INSERT [webpages_Roles] OFF;
GO
SET IDENTITY_INSERT [Permissions] ON;
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (1,N'AddPage',N'User can add new pages',N'Pages');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (2,N'EditPage',N'User can edit existing pages',N'Pages');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (3,N'DeletePage',N'User can delete pages',N'Pages');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (4,N'ArchivePage',N'User can archive or unarchive pages',N'Pages');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (5,N'PublishPage',N'User can publish or unpublish pages',N'Pages');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (6,N'AddMenu',N'User can create new menu widgets',N'Menus');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (7,N'AddItemsToMenu',N'User can add items to menu widgets',N'Menus');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (8,N'EditMenuItems',N'User can edit menu items',N'Menus');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (9,N'DeleteItemsFromMenu',N'User can delete items from menus',N'Menus');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (10,N'ManageWidgets',N'User can access the widget management area',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (11,N'AddHtmlWidget',N'User can create new HTML widgets',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (12,N'PlaceHtmlWidgets',N'User can place HTML widgets',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (13,N'PlaceMenus',N'User can plce menu widgets',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (14,N'PlaceFolders',N'User can place folder widgets',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (15,N'RemoveWidgets',N'User can remove placed widgets',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (16,N'CreateEmailForms',N'User can create new email form widgets',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (17,N'PlaceFormWidgets',N'User can place email form widgets',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (18,N'AddItemsToForms',N'User can add fields to form widgets',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (19,N'EditFormItems',N'User can edit email form widgets',N'Widgets');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (20,N'AddLayer',N'User can create new layers',N'Layers');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (21,N'ManagePagesInLayer',N'User can define which pages belong to a layer',N'Layers');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (22,N'CreateMediaFolder',N'User can create new media folders',N'Media');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (23,N'DeleteMediaFolder',N'User can delete media folders',N'Media');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (24,N'UploadMedia',N'User can upload media files',N'Media');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (25,N'CreateUser',N'User can create new users',N'Users');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (26,N'EditUser',N'User can edit a new user',N'Users');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (27,N'DeleteUser',N'User can deactivate users',N'Users');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (28,N'ActivateUser',N'User can reactivate users',N'Users');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (29,N'CreateRole',N'User can create a new role',N'Roles');
GO
INSERT INTO [Permissions] ([PermissionId],[Name],[Description],[Area]) VALUES (30,N'ManageRolesPermissions',N'User can assign permissions to a role',N'Roles');
GO
SET IDENTITY_INSERT [Permissions] OFF;
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,1);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,2);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,3);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,4);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,5);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,6);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,7);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,8);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,9);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,10);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,11);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,12);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,13);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,14);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,15);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,16);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,17);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,18);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,19);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,20);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,21);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,22);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,23);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,24);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,25);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,26);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,27);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,28);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,29);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (1,30);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (2,1);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (2,2);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (2,3);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (2,4);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (2,5);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (3,3);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (3,4);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (3,5);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (4,1);
GO
INSERT INTO [RolesPermissions] ([RoleId],[PermissionId]) VALUES (4,2);
GO
SET IDENTITY_INSERT [Pages] ON;
GO
INSERT INTO [Pages] ([PageId],[Title],[Url],[DateCreated],[IsHomePage],[MetaDescription],[MetaKeywords],[DateArchived],[ArchivedBy],[Layout]) VALUES (1,N'Home','',GetDate(),1,NULL,NULL,NULL,NULL,N'_SiteLayout.cshtml');
GO
SET IDENTITY_INSERT [Pages] OFF;
GO
SET IDENTITY_INSERT [PageContent] ON;
GO
INSERT INTO [PageContent] ([ContentId],[PageId],[DateCreated],[Published],[Content],[CreatedBy],[PublishedBy]) VALUES (1,1,GetDate(),GetDate(),N'<h1>Home</h1><p>Home page content</p>',1,1);
GO
SET IDENTITY_INSERT [PageContent] OFF;
GO
SET IDENTITY_INSERT [Menus] ON;
GO
INSERT INTO [Menus] ([MenuId],[MenuName],[CssClass],[Orientation]) VALUES (1,N'Main Menu',N'main-menu',N'Horizontal');
GO

SET IDENTITY_INSERT [Menus] OFF;
GO
SET IDENTITY_INSERT [MenuItems] ON;
GO
INSERT INTO [MenuItems] ([MenuItemId],[MenuId],[MenuText],[CssClass],[Target],[MenuOrder],[PageId]) VALUES (1,1,N'Home',NULL,NULL,0,1);
GO
SET IDENTITY_INSERT [MenuItems] OFF;
GO
SET IDENTITY_INSERT [Layers] ON;
GO
INSERT INTO [Layers] ([LayerId],[LayerName],[Description],[Layout]) VALUES (1,N'Default',N'Widgets placed in this layer will appear in all pages',N'_SiteLayout.cshtml');
GO
SET IDENTITY_INSERT [Layers] OFF;
GO
INSERT INTO [PagesInLayers] ([LayerId],[PageId]) VALUES (1,1);
GO
SET IDENTITY_INSERT [FieldDataTypes] ON;
GO
INSERT INTO [FieldDataTypes] ([DataTypeId],[DataType],[Description]) VALUES (1,N'Text',N'Suitable for short text inputs such as name or email');
GO
INSERT INTO [FieldDataTypes] ([DataTypeId],[DataType],[Description]) VALUES (2,N'LongText',N'For large text inputs such as comments or message');
GO
INSERT INTO [FieldDataTypes] ([DataTypeId],[DataType],[Description]) VALUES (3,N'Numeric',N'Will validate for numbers only');
GO
INSERT INTO [FieldDataTypes] ([DataTypeId],[DataType],[Description]) VALUES (4,N'Email',N'Will validate for a valid email pattern');
GO
INSERT INTO [FieldDataTypes] ([DataTypeId],[DataType],[Description]) VALUES (5,N'Web Address',N'Will validate for a valid URL pattern');
GO
INSERT INTO [FieldDataTypes] ([DataTypeId],[DataType],[Description]) VALUES (6,N'Submit',N'A button that will submit the form when clicked');
GO
SET IDENTITY_INSERT [FieldDataTypes] OFF;
GO

ALTER TABLE [Permissions] ADD CONSTRAINT [PK_Permissions_PermissionId] PRIMARY KEY ([PermissionId]);
GO
ALTER TABLE [Pages] ADD CONSTRAINT [PK_Pages_PageId] PRIMARY KEY ([PageId]);
GO
ALTER TABLE [PageContent] ADD CONSTRAINT [PK_PageContent_ContentId] PRIMARY KEY ([ContentId]);
GO
ALTER TABLE [Menus] ADD CONSTRAINT [PK_Menus_MenuId] PRIMARY KEY ([MenuId]);
GO
ALTER TABLE [MenuItems] ADD CONSTRAINT [PK_MenuItems_MenuItemId] PRIMARY KEY ([MenuItemId]);
GO
ALTER TABLE [Layers] ADD CONSTRAINT [PK_Layers_LayerId] PRIMARY KEY ([LayerId]);
GO
ALTER TABLE [HtmlWidgets] ADD CONSTRAINT [PK_HtmlWidgets_HtmlWidgetId] PRIMARY KEY ([HtmlWidgetId]);
GO
ALTER TABLE [Forms] ADD CONSTRAINT [PK_Forms_FormId] PRIMARY KEY ([FormId]);
GO
ALTER TABLE [Widgets] ADD CONSTRAINT [PK_Widgets_WidgetId] PRIMARY KEY ([WidgetId]);
GO
ALTER TABLE [FieldDataTypes] ADD CONSTRAINT [PK_FieldDataTypes_DataTypeId] PRIMARY KEY ([DataTypeId]);
GO
ALTER TABLE [FormFields] ADD CONSTRAINT [PK_FormFields_FormFieldId] PRIMARY KEY ([FormFieldId]);
GO
CREATE UNIQUE INDEX [UQ__webpages_Roles__00000000000001AB] ON [webpages_Roles] ([RoleName] ASC);
GO
CREATE UNIQUE INDEX [UQ__Users__000000000000016E] ON [Users] ([UserName] ASC);
GO
CREATE UNIQUE INDEX [IX_PagesInLayers_LayerId_PageId] ON [PagesInLayers] ([LayerId] ASC,[PageId] ASC);
GO
ALTER TABLE [RolesPermissions] ADD CONSTRAINT [FK_RolesPermissions_Permissions] FOREIGN KEY ([PermissionId]) REFERENCES [Permissions]([PermissionId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [RolesPermissions] ADD CONSTRAINT [FK_RolesPermissions_webpages_Roles] FOREIGN KEY ([RoleId]) REFERENCES [webpages_Roles]([RoleId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [PageContent] ADD CONSTRAINT [FK_PageContent_Pages] FOREIGN KEY ([PageId]) REFERENCES [Pages]([PageId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [MenuItems] ADD CONSTRAINT [FK_MenuItems_Menus] FOREIGN KEY ([MenuId]) REFERENCES [Menus]([MenuId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [MenuItems] ADD CONSTRAINT [FK_MenuItems_Pages] FOREIGN KEY ([PageId]) REFERENCES [Pages]([PageId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [PagesInLayers] ADD CONSTRAINT [FK_PagesInLayers_Layers] FOREIGN KEY ([LayerId]) REFERENCES [Layers]([LayerId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [PagesInLayers] ADD CONSTRAINT [FK_PagesInLayers_Pages] FOREIGN KEY ([PageId]) REFERENCES [Pages]([PageId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [Widgets] ADD CONSTRAINT [FK_Widgets_Forms] FOREIGN KEY ([FormId]) REFERENCES [Forms]([FormId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [Widgets] ADD CONSTRAINT [FK_Widgets_HtmlWidgets] FOREIGN KEY ([HtmlWidgetId]) REFERENCES [HtmlWidgets]([HtmlWidgetId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [Widgets] ADD CONSTRAINT [FK_Widgets_Layers] FOREIGN KEY ([LayerId]) REFERENCES [Layers]([LayerId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [Widgets] ADD CONSTRAINT [FK_Widgets_Menus] FOREIGN KEY ([MenuId]) REFERENCES [Menus]([MenuId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [FormFields] ADD CONSTRAINT [FK_FormFields_FieldDataTypes] FOREIGN KEY ([DataTypeId]) REFERENCES [FieldDataTypes]([DataTypeId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
ALTER TABLE [FormFields] ADD CONSTRAINT [FK_FormFields_Forms] FOREIGN KEY ([FormId]) REFERENCES [Forms]([FormId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO